package com.jhf.youke.hbase.domain.service;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;

/**
* @Description:
* @Param:
* @return:
* @Author: RHJ
* @Date: 2022/11/15
*/
@Component
public class PhoenixService {

    @Value("${phoenix.url}")
    private String url;

    private String phoenixDriver = "org.apache.phoenix.jdbc.PhoenixDriver";


    public Connection getConnection() throws Exception {
        Class.forName(phoenixDriver);
        Properties properties = new Properties();
        return  DriverManager.getConnection(url, properties);
    }

    /**
    * @Description:  创建表
    * @Param: [tableName]
    * @return: void
    * @Author: RHJ
    * @Date: 2022/12/12
    */
    public void createTable(String tableName,List<String> columns) throws Exception {
        //获取连接
        Connection connection = getConnection();
        // 创建Statement对象
        String sql = "CREATE TABLE IF NOT EXISTS " + tableName +"( "+
                "id VARCHAR primary key," ;

        for(int i=0; i<columns.size(); i++){
            if(i == columns.size()){
                sql += columns.get(i) + " VARCHAR)";
            }else{
                sql += columns.get(i) + " VARCHAR, ";
            }
        }

        PreparedStatement statement = connection.prepareStatement(sql);
        // 执行sql操作
        statement.execute();
        // 关闭
        statement.close();
        connection.close();
    }

    /**
    * @Description:  删除表
    * @Param: [tableName]
    * @return: void
    * @Author: RHJ
    * @Date: 2022/12/12
    */
    public void dropTable(String tableName) throws Exception {
        //获取连接
        Connection connection = getConnection();
        // 创建Statement对象
        String sql = "DROP TABLE "+ tableName;
        PreparedStatement statement = connection.prepareStatement(sql);
        // 执行sql操作
        statement.execute();
        // 关闭
        statement.close();
        connection.close();
    }


    public void insertData(String tableName, String values) throws Exception {
        //获取连接
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        statement.executeUpdate("upsert into "+ tableName +" values("+ values +")");
        connection.commit();
        statement.close();
        connection.close();
    }

        /**
        * @Description:
        * @Param: []
        * @return: void
        * @Author: RHJ
        * @Date: 2022/12/12
        */
    public void insertBatchData(String tableName, List<String> values) throws Exception {
        //获取连接
        Connection connection = getConnection();

        //获取Statement对象，并进行数据插入
        Statement statement = connection.createStatement();

        for(String value : values){
            statement.executeUpdate("upsert into "+ tableName +" values("+ values +")");
        }
        connection.commit();
        statement.close();

//        //获取PreparedStatement对象，并进行数据插入
//        PreparedStatement preparedStatement = connection.prepareStatement(
//                "upsert into \"student\" values(?,?,?)");
//        //给参数赋值
//        preparedStatement.setString(1,"1003");
//        preparedStatement.setString(2,"hangge");
//        preparedStatement.setString(3,"1000");
//        //执行插入
//        preparedStatement.execute();
//        connection.commit();
//        preparedStatement.close();

        connection.close();
    }

    /**
    * @Description:  删除数据
    * @Param: []
    * @return: void
    * @Author: RHJ
    * @Date: 2022/12/12
    */
    public void deleteData(String tableName, String id) throws Exception {
        //获取连接
        Connection connection = getConnection();

        //获取Statement对象，并进行数据删除
        Statement statement = connection.createStatement();
        statement.execute("delete from "+ tableName +" where id = " + id);
        connection.commit();
        statement.close();
        connection.close();
    }

    /**
    * @Description:
    * @Param: [tableName]
    * @return: java.util.List<java.util.Map<java.lang.String,java.lang.String>>
    * @Author: RHJ
    * @Date: 2022/12/12
    */
    public List<Map<String, String>> getData(String tableName) throws Exception {
        //获取连接
        Connection connection = getConnection();
        String sql = "SELECT * FROM " + tableName;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        List<Map<String, String>> resultList = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, String> result = new HashMap<>();
            for (int i = 1, len = resultSetMetaData.getColumnCount(); i <= len; i++) {
                result.put(resultSetMetaData.getColumnName(i), resultSet.getString(i));
            }
            resultList.add(result);
        }
        return resultList;
    }



    public static void main(String[] args)throws Exception {



    }

}
